<?php
/*
classe boite à outils MySQL

file version 13/09/2005 15:56:07

history :
2005-06-17 14:05:29
	adding setEncoding(), and automatic encoding setting at connection opening if provided in constructor
15/09/2005 15:39:35
	corrected secureValues, only '' and NULL are converted AS NULL
*/

class mysqlTool {
/*----------------------------------------------------------------------------------------------
                                                                                      proprietes
----------------------------------------------------------------------------------------------*/
	var $link;
	var $log=array();
	/* log des operations, tableaux sous la forme
	'query'=>requete sql, 'ok'=>booleen, 'error'=>erreurs eventuelles, 'count'=> nbre de lignes affectees
	*/
	var $base;
	var $domain;
	var $login="";
	var $pwd="";
	var $encoding=NULL;
/*----------------------------------------------------------------------------------------------
                                                                                    constructeur
----------------------------------------------------------------------------------------------*/
	function mysqltool($domain=NULL,$login=NULL,$pwd=NULL,$base=NULL,$encoding=NULL) {
		$this->domain=$domain;
		$this->login=$login;
		$this->pwd=$pwd;
		$this->base=$base;
		$this->encoding=$encoding;
	}
	
	function & getSingleton() {
		$class=__CLASS__; // getting current class name
		// instanciation unique
		if (!isset($GLOBALS['singletons'][$class])) {
			// getting arguments to forward
			$args=func_get_args();
			/* building instanciation code, forwarding arguments
			creating singleton and a single global reference per class name */
			$eval='$GLOBALS[\'singletons\'][$class]=& new $class(';
			// adding arguments to constructor
			for ($f=0; $f<count($args); $f++) $eval.='$args['.$f.'],';
			// deleting last unnecessary ',' and closing call
			$eval=substr($eval,0,-1).');';
			// executing instanciation code
			eval($eval);
		}
		// throwing instance
		return $GLOBALS['singletons'][$class];
	}
/*----------------------------------------------------------------------------------------------
                                                                              methodes publiques
----------------------------------------------------------------------------------------------*/
	// ouverture de la connexion (si necessaire)
	function open($domain=NULL,$login=NULL,$pwd=NULL,$base=NULL) {
		//echo affvar($this->link);
		if ($this->link) return true;
		switch (true) {
			// arguments obligatoires absents
			case (!$domain and !$this->domain):
			case (!$login and !$this->login):
			//case (!$pwd and !$this->pwd):
				trigger_error("mysqlTool->open couldn't open connection without parameters domain, login, pwd",E_USER_WARNING);
				return false;
				break;
			// utilisation des arguments par defaut s'il ne sont pas fournis
			// pas de break; si un argument n'est pas fourni ceux à sa droite ne le sont pas non plus
			case (!$domain):
				$domain=&$this->domain;
			case (!$login):
				$login=&$this->login;
			case (!$pwd):
				$pwd=&$this->pwd;
			case ($base===NULL):
				$base=&$this->base;
				break;
		}
		if (!$this->link=@mysql_connect($domain,$login,$pwd)) {
			trigger_error("mysqlTool->open couldn't connect to host ".$this->domain." ".strlen($login)."/".strlen($pwd),E_USER_WARNING);
			return false;
		}
		// trigger_error("mysqlTool->open connected to host ".$this->domain,E_USER_NOTICE);
		// auto fermeture
		register_shutdown_function('mysql_close');
		// setting encoding for next db transactions
		if ($this->encoding) $this->setEncoding($this->encoding);
		// selection de la base sauf indication contraire
		if (!$base) return true;
		if ($base!=NULL and !$this->setDb($base)) return false;
		return true;
	}
	// selection de base
	function setDb($base) {
		if (!$this->link/* and !$this->open($base)*/) {
			trigger_error('mysqlTool->setDb: no link');
			return false;
		} else if (!@mysql_select_db($base, $this->link)) {
			trigger_error('mysqlTool->setDb: failed to select'.$base,E_USER_WARNING);
			return false;
		}
		return true;
	}
	// fermeture de la connexion
	function close() {
		if (!$this->link) return true;
		return mysql_close($link);
	}
	// renvoie des infos sur la structure
	// recup de la structure de la table
	function & tableStructure($table) {
		// requete de selection des infos
		$sql="SHOW COLUMNS FROM ".$table;
		if (!$result=$this->result($sql,'Field')) {
			//echo $sql;
			return false;
		}
		// analyse et completion des resultats
		$result=$this->moreStructure($result);
		return $result;
	}
	// tableau de resultats
	function & result($sql,$indexField=NULL) {
		$r=false;
		if (!$result=$this->execute($sql))
			return $r;
		$table=array();
		// indexing on primary key
		/*if ($indexField===true)
			$indexField=$this->tablePrimaryKey*/
		while ($line=@mysql_fetch_array($result,MYSQL_ASSOC)) {
			// indexation sur un champ
			if ($indexField and isset($line[$indexField]))
				$table[$line[$indexField]]=$line;
			else $table[]=$line;
		}
		if (count($table)==0)
			trigger_error("mysqlTool->tableStructure: no result line for ".$sql,E_USER_NOTICE);
		return $table;
	}
	// tableau de résultat unique
	function & uniqueResult($sql) {
		$r=false;
		if (!$result=$this->execute($sql))
			return $r;
		if (!$table=@mysql_fetch_array($result,MYSQL_ASSOC)) {
			trigger_error("mysqlTool->uniqueResult: no result line for ".$sql,E_USER_NOTICE);
			return $r;
		}
		if (@mysql_fetch_array($result,MYSQL_ASSOC))
			trigger_error("mysqlTool->uniqueResult: more than 1 line for ".$sql,E_USER_NOTICE);
		return $table;
	}
	// execution de requete
	function execute($sql) {
		//echo 'sqlManager: '.$sql.'<br />';
		if (!$this->link) $this->open();
		//echo "<br />SQL link : ".affvar($this->link);
		if (!$this->link) {
			$this->log($sql,false,0,'no link to execute query');
			trigger_error("mysqlTool->execute: no link to execute query",E_USER_WARNING);
			return false;
		}
		//echo affvar($this->link);
		//echo $sql;
		if ($result=@mysql_query($sql,$this->link)) {
			$this->log($sql,true);
			return $result;
		}
		//echo 'sqlManager: '.$sql.' failed, '.mysql_error().affvar($result).'<br />';
		trigger_error("mysqlTool->execute: ".$sql.":<br /> ".mysql_error(),E_USER_NOTICE);
		$this->log($sql,false);
		return false;
	}
	// renvoie une valeur "sécurisée" à utiliser dans une requête sql.
	// ajoute des guillemets simples atour de la valeur
	function & secureValue($value) {
		//if (!is_string($value)) $value=settype()
		if (is_array($value)) $value=join(',',$value);
		//if (!get_magic_quotes_gpc()) $value=addslashes($value);
		if ($value==NULL or $value=='') return 'NULL';
		// if (is_numeric($value)) return strval($value);
		$value=ereg_replace("([^\\])'","\\1\'",$value);
		$r="'".$value."'";
		return $r;
	}
	/* insère un tableau associatif en tant que ligne d'enregistrement
	les clefs  doivent correspondre aux noms de champ MySQL
	les valeurs nulles seront ignorées par défaut
	*/
	function insertArray($table,$data=array(),$explicitNegative=false) {
		// preparation du tableau de données
		$newdata=array();
		foreach ($data as $key => $value) {
			// on ignore les valeurs negatives : false, 0, NULL, ''
			if (!$value and !$explicitNegative) continue;
			switch (true) {
			case is_string($value): $value=$this->secureValue($value);
				break;
			case is_null($value): $value='NULL';
				break;
			case is_bool($value): if ($value) $value=1; else $value=0;
				break;
			default: // unsupported data type
				break;
			}
			$newdata[$key]=$value;
		}
		$sql="INSERT INTO $table(".join(array_keys($newdata),',').")"
		." VALUES(".join($newdata,',').")";
		//echo affvar($sql,'sql',false);
		return $this->execute($sql);
	}
	
	// renvoie un resumé de la dernire operation
	// mode = array|string
	function lastOpSummary($mode='string') {
		$last=end($this->log);
		switch ($mode) {
			case 'string':
			default:
				return "SQL: ".$last['query']."\n"
				."result: ".strval($last['ok'])."\n"
				."lines: ".$last['count']."\n"
				."error: ".$last['error']."\n";
				break;
		}
	}
	
	/* setting encoding for db transactions
	*/
	function setEncoding($encoding) {
		// MySQL 4.0
		if (!$this->execute("SET CHARACTER SET '$encoding'")) return false;
		// MySQL 4.1
		if (!$this->execute("SET NAMES '$encoding'")) return false;
		// echo "<p>encoding $encoding set for db transactions</p>";
		return true;
	}
	
	/* returns name of the primary key column of the table
	*/
	function tablePrimaryKey($table) {
		if (!$structure=$this->tableStructure($table)) return false;
		foreach($structure as $name => $field)
			if ($field['Key']=='PRI') return $name;
		return false;
	}
	
/*-------------------------------------------------------------------------------------------------
																												private methods
-------------------------------------------------------------------------------------------------*/
	// recup de la longueur du champ
	function getFieldLength($type="") {
		$length=array();
		// expression reguliere avec parentheses capturantes pour recuperer la longueur
		if (!ereg("^.*\(([0-9]*)\).*$",$type,$length)) return "";
		return $length[1];
	}
	
	// recup des choix d'un champ enum
	function getEnumChoices($type="") {
		$values=array();
		// expression reguliere avec parentheses capturantes pour recuperer les valeurs
		if (!ereg("^.*\((.*)\).*$",$type,$values)) return array();
		//echo $values;
		$values=explode(",",$values[1]);
		// suppression des quotes
		for ($f=0; $f<count($values); $f++)
			$values[$f]=substr($values[$f],1,strlen($values[$f])-2);
		return $values;
	}
	
	// enrichit la table de structure retournÚe par show columns
	// en decomposant le champ Type
	function & moreStructure(& $structure) {
		$table=array();
		for (reset($structure); $key=key($structure); next($structure)) {
			$info=current($structure);
			$table[$key]=array_merge($info,$this->analyzeType($info['Type']));
		}
		return $table;
	}
	
	function analyzeType($type) {
		$info=array();	
		if (stristr($type,'unsigned')) {
			$info['Unsigned']='YES';
			$type=str_replace('unsigned','',$type);
		}
		if (stristr($type,'zerofill')) {
			$info['Zerofill']='YES';
			$type=str_replace('zerofill','',$type);
		}
		if (stristr($type,'binary')) {
			$info['Binary']='YES';
			$type=str_replace('binary','',$type);
		}
		$inside_parenthesis=array();
		switch (true) {
			// type entier, char ou timestamp
			case (ereg('.*int\((.*)\)', $type,$inside_parenthesis)
			or ereg('.*char\((.*)\)', $type,$inside_parenthesis)
			or (ereg('^timestamp\((.*)\)', $type,$inside_parenthesis))): // type entier ou char
				$info['Length']=intval($inside_parenthesis[1]);
				// enlevement des parentheses
				$type=substr($type,0,strpos($type,'('));
				break;
			// type decimal, float ou numeric
			case (ereg('^decimal\((.*)\)',$type,$inside_parenthesis)
			or ereg('^float\((.*)\)',$type,$inside_parenthesis)
			or ereg('^numeric\((.*)\)',$type,$inside_parenthesis)):
				$parts=explode(',',$inside_parenthesis[1]);
				$info['Length']=$parts[0];
				$info['Decimals']=$parts[1];
				// enlevement des parentheses
				$type=substr($type,0,strpos($type,'('));
				break;
			// type enum ou set
			case (ereg('^enum\((.*)\)',$type,$inside_parenthesis)
			or ereg('^set\((.*)\)',$type,$inside_parenthesis)):
				// recup des valeurs
				$values=explode(',',$inside_parenthesis[1]);
				// enlevement des quotes
				for ($f=0; $f<count($values); $f++)
					$values[$f]=substr(substr($values[$f],0,-1),1);
				//ereg(".*'(.*)'.*",$inside_parenthesis[1],$values);
				$info['Values']=$values;
				// enlevement des parentheses
				$type=substr($type,0,strpos($type,'('));
				break;
		}
		$info['Type']=trim($type);
		if (!isset($info['Length'])) $info['Length']=NULL;
		if (!isset($info['Decimals'])) $info['Decimals']=NULL;
		if (!isset($info['Values'])) $info['Values']=array();
		return $info;
	}
	
	/*function secureQuery($query) {
		// recherche des clauses where
		$name="[a-z0-9_-]+"; $value=".*"
		$regex="where (($name='?$value'?))"
		if (eregi($regex,$query,$found)) {
			echo affvar($found);
		}
		return $query;
	}*/
	function log($sql,$bool=false,$count=NULL,$error=NULL) {
		if ($count===NULL) $count=mysql_affected_rows();
		if (!$bool and $error===NULL) $error=mysql_error();
		$this->log[]=array(
			'query'=>$sql,
			'ok'=>$bool,
			'error'=>$error,
			'count'=>$count
		);
	}
/*----------------------------------------------------------------------------------------------
                                                                                      proprietes
----------------------------------------------------------------------------------------------*/
}
?>
